<?php

/**
 * Excel Helper
 * 
 * This helper will make output excel 2007 file
 * 
 * @author OanhNN
 * @package Cake.View.Helper
 * @link http://bakery.cakephp.org/articles/melgior/2010/01/26/simple-excel-spreadsheet-helper
 */
App::import('Vendor', 'PHPExcel', array('file' => 'phpexcel/PHPExcel.php'));
App::import('Vendor', 'PHPExcelWriter', array('file' => 'phpexcel/PHPExcel/Writer/Excel2007.php'));
//App::import('Vendor','PHPExcelWriter',array('file' => 'phpexcel/PHPExcel/Writer/Excel5.php'));

App::uses('AppHelper', 'View/Helper');

class ExcelHelper extends AppHelper {

    /**
     * @var PHPExcel Object
     * @access protected
     */
    protected $_xls;

    /**
     * @var PHPExcel_worksheet
     */
    protected $_sheet;

    /**
     * @var array
     * @access protected
     */
    protected $_data;

    /**
     * @var array
     * @access protected
     */
    protected $_blacklist = array();

    /**
     * Constructor.
     *
     * @access public
     */
    function __construct(View $View, $settings = array()) {
        parent::__construct($View, $settings);
        $this->_xls = new PHPExcel();
        $this->_sheet = $this->_xls->getActiveSheet();
        $this->_sheet->getDefaultStyle()->getFont()->setName('Verdana');
    }

    /**
     * Generate execl file from data
     * 
     * @access public
     * @param  array   $data
     * @param  string  $title default 'Report'
     * @return void
     */
    public function generate(&$data, $title = 'Report') {
        $this->_data = & $data;
        $this->_title($title);
        $this->_headers();
        $this->_rows();
        $this->_output($title);
        return;
    }

    /**
     * Set title for excel file
     * 
     * @access protected
     * @param  string $title
     * @return void
     */
    protected function _title($title) {
        $this->_sheet->setCellValue('A2', $title);
        $this->_sheet->getStyle('A2')->getFont()->setSize(14);
        $this->_sheet->getRowDimension('2')->setRowHeight(23);
        return;
    }

    /**
     * Set headers for excel file
     * 
     * @access protected
     * @return void
     */
    protected function _headers() {
        $i = 0;
        foreach ($this->_data[0] as $field => $value) {
            if (!in_array($field, $this->_blacklist)) {
                $columnName = Inflector::humanize($field);
                $this->_sheet->setCellValueByColumnAndRow($i++, 4, $columnName);
            }
        }
        $this->_sheet->getStyle('A4')->getFont()->setBold(true);
        $this->_sheet->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $this->_sheet->getStyle('A4')->getFill()->getStartColor()->setRGB('969696');
        $this->_sheet->duplicateStyle($this->_sheet->getStyle('A4'), 'B4:' . $this->_sheet->getHighestColumn() . '4');
        for ($j = 1; $j < $i; $j++) {
            $this->_sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($j))->setAutoSize(true);
        }
        return;
    }

    /**
     * Set row for excel file
     * 
     * @access protected
     * @return void
     */
    protected function _rows() {
        $i = 5;
        foreach ($this->_data as $row) {
            $j = 0;
            foreach ($row as $field => $value) {
                if (!in_array($field, $this->_blacklist)) {
                    $this->_sheet->setCellValueByColumnAndRow($j++, $i, $value);
                }
            }
            $i++;
        }
        return;
    }

    /**
     * Make excel file
     * 
     * @access protected
     * @param  string $title
     * @return void
     */
    protected function _output($title) {
        header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = new PHPExcel_Writer_Excel2007($this->_xls);
        $objWriter->save('php://output');
        return;
    }

//    /**
//     * Make excel file
//     * 
//     * @access protected
//     * @param  string $title
//     * @return void
//     */
//    protected function _output($title) {
//        header("Content-type: application/vnd.ms-excel");
//        header('Content-Disposition: attachment;filename="' . $title . '.xls"');
//        header('Cache-Control: max-age=0');
//        $objWriter = new PHPExcel_Writer_Excel5($this->_xls);
//        $objWriter->setTempDir(TMP);
//        $objWriter->save('php://output');
//    }
}

